package com.ruoze.bdp.function;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruoze.bdp.constants.DataSourceConstants;
import com.ruoze.bdp.entity.TableStructVo;
import com.ruoze.bdp.entity.datasource.JDBCConnectionParamDTO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public abstract class JDBCDataSourceFunction implements DataSourceFunction {

    private Connection getConnection(String url, String user, String password) {
        try {
            Class.forName(getDriverName());
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            return null;
        }
    }

    private JSONArray query(String sql, String connectInfo) {
        JDBCConnectionParamDTO paramDTO = JSONObject.parseObject(connectInfo, JDBCConnectionParamDTO.class);

        Connection connection = null;
        PreparedStatement preparedStatement;
        try {
            connection = getConnection(paramDTO.getUrl(), paramDTO.getUser(), paramDTO.getPassword());
            if (connection == null) {
                return null;
            }
            preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();
            JSONArray results = new JSONArray();
            while (resultSet.next()) {
                JSONObject jsonObject = new JSONObject();//将每一个结果集放入到jsonObject对象中
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    jsonObject.put(metaData.getColumnName(i).toUpperCase(), resultSet.getObject(i));//列值一一对应
                }
                results.add(jsonObject);
            }
            return results;
        } catch (Exception e) {
            return null;
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    private void create(String sql, String connectInfo) {
        JDBCConnectionParamDTO paramDTO = JSONObject.parseObject(connectInfo, JDBCConnectionParamDTO.class);

        Connection connection = null;
        PreparedStatement preparedStatement;
        try {
            connection = getConnection(paramDTO.getUrl(), paramDTO.getUser(), paramDTO.getPassword());
            if (connection == null) {
                return;
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
        } catch (Exception e) {
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    @Override
    public boolean connectTest(String info) {
        JSONArray results = query(getValidationQuery(), info);
        try {
            if (results != null) {
                return results.getJSONObject(0).getLong("1") == 1;
            }
            return false;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public List<String> getMetaDatabases(String info) {
        JSONArray queryArrays = query(getMetaDatabasesQuery(), info);
        List<String> databases = new ArrayList<>();
        try {
            if (queryArrays != null && queryArrays.size() > 0) {
                for (int i = 0; i < queryArrays.size(); i++) {
                    String databaseName = queryArrays.getJSONObject(i).getString(getMetaDatabasesQueryColumn().toUpperCase());
                    databases.add(databaseName);
                }
            }
        } catch (Exception e) {
            return null;
        }
        return databases;
    }

    @Override
    public List<String> getMetaTables(String info, String database) {
        JSONArray queryArrays = query(getMetaTablesQuery(database), info);
        List<String> tables = new ArrayList<>();
        try {
            if (queryArrays != null && queryArrays.size() > 0) {
                for (int i = 0; i < queryArrays.size(); i++) {
                    String tableName = queryArrays.getJSONObject(i).getString(getMetaTablesQueryColumn().toUpperCase());
                    tables.add(tableName);
                }
            }
        } catch (Exception e) {
            return null;
        }
        return tables;
    }

    @Override
    public List<Map<String, String>> getMetaColumns(String info, String database, String table) {
        JSONArray queryArrays = query(getMetaColumnsQuery(database, table), info);
        List<Map<String, String>> columns = new ArrayList<>();
        try {
            if (queryArrays != null && queryArrays.size() > 0) {
                for (int i = 0; i < queryArrays.size(); i++) {
                    Map object = queryArrays.getJSONObject(i).toJavaObject(Map.class);
                    columns.add(object);
                }
            }
        } catch (Exception e) {
            return null;
        }
        return columns;
    }

    @Override
    public boolean createDatabase(String info, String name) {
        create(getCreateDatabaseExecQuery(name), info);
        return getMetaDatabases(info).contains(name);
    }

    @Override
    public boolean createTable(String info, TableStructVo tableVo) {
        String databaseName = tableVo.getDatabaseName();
        String tableName = tableVo.getTableName();
        String name = String.join(".", databaseName, tableVo.getTableName());
        create(getCreateTableExecQuery(name, tableVo.getColumnDefinitionList(), tableVo.getTableOptions()), info);
        return getMetaTables(info, databaseName).contains(tableName);
    }

    public abstract String getDriverName();

    public abstract String getValidationQuery();

    public abstract String getMetaDatabasesQuery();

    public abstract String getMetaDatabasesQueryColumn();

    public abstract String getMetaTablesQuery(String database);

    public abstract String getMetaTablesQueryColumn();

    public abstract String getMetaColumnsQuery(String database, String table);

    public String getCreateDatabaseExecQuery(String name) {
        return String.format(DataSourceConstants.JDBC_CREATE_DATABASE_EXEC_QUERY, name);
    }

    public abstract String getCreateTableExecQuery(String name,
                                                   List<Map<String, String>> columnDefinitionList,
                                                   Map<String, Object> tableOptions);
}
